package com.novoda.runbuddy.provider;

public class RouteDBSQL {

	public static final String	ROUTE_POINT_TABLE			= "routePoint";

	public static final String	ROUTE_TABLE					= "route";

	public static final String	ROUTE_ID					= "_id";
	public static final String	ROUTE_SID					= "__id";
	public static final String	ROUTE_GEOLOCATION_ID		= "_idGeoLocation";
	public static final String	ROUTE_NAME					= "name";
	public static final String	ROUTE_CREATED_AT			= "createdAt";
	public static final String	ROUTE_MODIFIED_AT			= "modifiedAt";

	public static final String	ROUTEPOINT_POINT_ID			= "_id";
	public static final String	ROUTEPOINT_ROUTE_ID			= "_routePointId";
	public static final String	ROUTEPOINT_GEOLOCATION_ID	= "_idGeoLocation";
	public static final String	ROUTEPOINT_POINTNO			= "_idRoute";
	public static final String	ROUTEPOINT_DIRECTION		= "_idDirection";
	public static final String	ROUTEPOINT_ALGORITHM		= "algorithm";
	public static final String	ROUTEPOINT_CREATED_AT		= "createdAt";
	public static final String	ROUTEPOINT_MODIFIED_AT		= "modifiedAt";

	public static String[]		COLUMNS_IN_ROUTE_ENTRY		= new String[] { ROUTE_ID, ROUTE_SID, ROUTE_NAME, ROUTE_GEOLOCATION_ID,
			ROUTE_CREATED_AT, ROUTE_MODIFIED_AT			};

	public static String[]		COLUMNS_IN_ROUTEPOINT_ENTRY	= new String[] { ROUTEPOINT_POINT_ID, ROUTEPOINT_ROUTE_ID,
			ROUTEPOINT_GEOLOCATION_ID, ROUTEPOINT_POINTNO, ROUTEPOINT_DIRECTION, ROUTEPOINT_ALGORITHM, ROUTEPOINT_CREATED_AT,
			ROUTEPOINT_MODIFIED_AT							};

	public static String insertRouteJogId(String jogId) {
		return "INSERT INTO route (_jogId) " + "VALUES ( \'" + jogId + "\');";
	}

	public static String selectJustEnteredRouteId(String jogId) {
		return "SELECT _id " + "FROM route " + "WHERE _jogId = " + jogId + " ORDER BY _id DESC " + "LIMIT 1";
	}

	public static String selectJustEnteredRoutePointId(String routeId) {
		return "SELECT _id FROM routePoint WHERE _routeId = " + routeId + " ORDER BY _id DESC LIMIT 1";
	}

	public static String insertRoutePoint(String pointNo, String _routeId, String latitude, String longitude) {
		return "INSERT INTO routePoint (pointNo, _routeId, latitude, longitude ) VALUES ( \'" + pointNo + "\',\'" + _routeId
				+ "\',\'" + latitude + "\',\'" + longitude + "\')";

	}

	public static final String	GET_LAST_ROUTE_POINT_FROM_JOG	= "SELECT time FROM time" + "WHERE _routeId = ("
																		+ "	SELECT _id FROM route " + "	WHERE _jogId = 1 " + "	)"
																		+ "ORDER BY time DESC LIMIT 1;";

}
